﻿using System;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
	/**
	 * Classname : Nguoidung
	 * Version information :1.0
	 * Date  :26-09-2006
	 * Copyright notice : 
	 */
	
	public class DANguoidung : DbObject
	{
        protected SqlConnection connec;

        public DANguoidung(SqlConnection Connection)
		{
            connec = Connection;
		}
        public DataSet loadNguoiDungChucNang(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoiDungID;

            DataSet dsNguoidung = RunProcedure("sp_NguoiDungChucNang_loadNguoiDungChucNang", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
        public DataSet loadNguoiDungLevel(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoiDungID;

            DataSet dsNguoidung = RunProcedure("sp_NguoiDungLevel_loadNguoiDungLevel", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }

        /// <summary>
        /// Created by LinhDH
        /// Cập nhật người dùng cấp trên cho nhóm người dùng
        /// </summary>
        /// <param name="NguoiDungIDs"></param>
        /// <param name="NguoiDungCapTrenID"></param>
        /// <returns></returns>
        public int updateTruongNhomNguoiDung(string NguoiDungIDs, int NguoiDungCapTrenID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	 new SqlParameter("@NguoiDungIDs", SqlDbType.VarChar,2000),
                                              new SqlParameter("@NguoiDungCapTrenID", SqlDbType.Int)
										 };
            // set the values
            parameters[0].Value = NguoiDungIDs;
            parameters[1].Value = NguoiDungCapTrenID;
            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_updateTruongNhomNguoiDung", parameters, out numAffected, connec);

            return numAffected;
        }

        /// <summary>
        /// Created by LinhDH 27/04/2012
        /// </summary>
        /// <returns></returns>
        public int loadIdNguoidungByUserName(string ip_str_user)
        {
            // create the parameter
            SqlParameter[] parameters =  {	 new SqlParameter("@UserName", SqlDbType.VarChar, 100)
										 };
            parameters[0].Value = ip_str_user;
            DataSet dsUser = RunProcedure("sp_Nguoidung_loadNguoidungByUserName", parameters, "TB_Nguoidung", connec);
            for (int i = 0; i < dsUser.Tables[0].Rows.Count; i++)
            {
                dsUser.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            DataRow[] v_dr = dsUser.Tables[0].Select("TenDangNhap ='" + ip_str_user + "'");
            // nghĩa là có cái username đó
            if (v_dr.Length > 0) return int.Parse(v_dr[0]["ID"].ToString());
            else return 0;
        }
        public DataSet loadQuyenNguoiDungCapDuoiByNguoiDungCapTrenVaTvts(int NguoiDungID, int ip_id_tvts)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),
                                          new SqlParameter("@ID_TVTS", SqlDbType.Int, 4)};
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = ip_id_tvts;

            DataSet dsNguoidung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungCapDuoiByNguoiDungCapTrenAndTvts", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
        public DataSet loadQuyenNguoiDungCapDuoiByNguoiDung(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4)};
            parameters[0].Value = NguoiDungID;

            DataSet dsNguoidung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungCapDuoiByNguoiDung", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
        public DataSet loadQuyenNguoiDungCapDuoiByNguoiDungNoAdmin(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoiDungID;

            DataSet dsNguoidung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungCapDuoiByNguoiDungNoAdmin", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
		// Kiem tra mat khau nguoi dung
        public int KiemTraMatKhau(string TenDangNhap, string MatKhau)
		{
			int numAffected;
			// create the parameters
			SqlParameter[] parameters =  {	 new SqlParameter("@TenDangNhap", SqlDbType.VarChar, 64),
											 new SqlParameter("@MatKhau", SqlDbType.VarChar , 64),
											 new SqlParameter("@ID", SqlDbType.Int)
										 };
			// set the values
			parameters[0].Value = TenDangNhap;
            parameters[1].Value = MatKhau;
			parameters[2].Direction = ParameterDirection.Output;
			// run the procedure
            RunProcedure("sp_Nguoidungs_CheckPass", parameters, out numAffected, connec);
			return (int)parameters[2].Value;
		}
        public DataSet loadNguoidung(string i_str_ho_va_ten,string i_str_ten_dang_nhap)
        {
            // create the parameter
            SqlParameter[] parameters = { 
                                            new SqlParameter("@ho_va_ten", SqlDbType.NVarChar, 100),
											new SqlParameter("@ten_dang_nhap", SqlDbType.VarChar , 50),
                                        };
            //set value
            parameters[0].Value = i_str_ho_va_ten;
            parameters[1].Value = i_str_ten_dang_nhap;
            DataSet dsUser = RunProcedure("sp_Nguoidung_loadNguoidung",parameters, "TB_Nguoidung", connec);
            for (int i = 0; i < dsUser.Tables[0].Rows.Count; i++)
            {
                dsUser.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsUser;
        }
        public DataSet load_tu_van_tuyen_sinh()
        {
            // create the parameter
            DataSet dsUser = RunProcedure("sp_Nguoidung_loadAllTVTS", "TB_Nguoidung", connec);
            return dsUser;
        }
        //public DataSet load_tu_van_tuyen_sinh()
        //{
        //    // create the parameter
        //    DataSet dsUser = RunProcedure("sp_Nguoidung_load_nhom", "TB_Nguoidung", connec);
        //    return dsUser;
        //}
        // return only the record with the specified ID!!!!!!!!!!!
        public DataSet loadNguoidungIDs(int NguoidungId)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoidungId;

            DataSet categorys = RunProcedure("sp_Nguoidung_loadNguoidungID", parameters, "TB_Nguoidung", connec);
                return categorys;
        }
        // return only the record with the specified ID!!!!!!!!!!!
        public DataRow loadNguoidungID(int NguoidungId)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoidungId;

            using (DataSet categorys = RunProcedure("sp_Nguoidung_loadNguoidungID", parameters, "TB_Nguoidung", connec))
            {
                return categorys.Tables[0].Rows[0];
            }
        }
        // update the properties of the specified Categorys
        // Sua noi dung ban ghi hien hanh
        public int updateNguoidung(int NguoidungId, string TenDangNhap, string MatKhau, string HoTen)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	    new SqlParameter("@ID", SqlDbType.Int, 4),
											    new SqlParameter("@TenDangNhap", SqlDbType.VarChar, 64),
											    new SqlParameter("@MatKhau", SqlDbType.VarChar, 64),
											    new SqlParameter("@HoTen", SqlDbType.NVarChar, 128)
										};
            // set the values
            parameters[0].Value = NguoidungId;
            parameters[1].Value = TenDangNhap.Trim();
            parameters[2].Value = MatKhau.Trim();
            parameters[3].Value = HoTen.Trim();
            // run the procedure
            RunProcedure("sp_Nguoidung_updateNguoidung", parameters, out numAffected, connec);

            return numAffected;
        }
        // insert noi dung ban ghi hien hanh
        public int insertNguoidung(string TenDangNhap, string MatKhau, string HoTen)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { 	     new SqlParameter("@TenDangNhap", SqlDbType.VarChar, 64),
											     new SqlParameter("@MatKhau", SqlDbType.VarChar, 64),
											     new SqlParameter("@HoTen", SqlDbType.NVarChar, 128),
                                                 new SqlParameter("@ID", SqlDbType.Int ,4)
										};
            // set the values
            parameters[0].Value = TenDangNhap.Trim();
            parameters[1].Value = MatKhau.Trim();
            parameters[2].Value = HoTen.Trim();
            parameters[3].Direction = ParameterDirection.Output;
            // run the procedure
            RunProcedure("sp_Nguoidung_insertNguoidung", parameters, out numAffected, connec);

            return (int)parameters[3].Value;
        }
        //Xoa Nguoidung
        public int removeUser(int ID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int) };
            parameters[0].Value = ID;
            // run the procedure
            RunProcedure("sp_Nguoidung_removeNguoidung", parameters, out numAffected, connec);
            return numAffected;
        }
        //QuyenNguoidung
        public int insertQuyenNguoiDung(int NguoiDungID, int QuyenID, int NguoiDungCapTrenID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	 new SqlParameter("@NguoiDungID", SqlDbType.Int),
											 new SqlParameter("@QuyenID", SqlDbType.Int),
                                              new SqlParameter("@NguoiDungCapTrenID", SqlDbType.Int)
										 };
            // set the values
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = QuyenID;
            parameters[2].Value = NguoiDungCapTrenID;

            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_insertQuyenNguoiDung", parameters, out numAffected, connec);

            return numAffected;
        }
        //Update QuyenNguoidung
        public int updateQuyenNguoiDung(int NguoiDungID, int QuyenID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	 new SqlParameter("@NguoiDungID", SqlDbType.Int),
											 new SqlParameter("@QuyenID", SqlDbType.Int)
										 };
            // set the values
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = QuyenID;
            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_updateQuyenNguoiDung", parameters, out numAffected, connec);

            return numAffected;
        }
        //QuyenNguoidung
        public int insertQuyenNguoiDungByQuyen(int NguoiDungID, int QuyenID, int NguoiDungCapTrenID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	 new SqlParameter("@NguoiDungID", SqlDbType.Int),
											 new SqlParameter("@QuyenID", SqlDbType.Int),
                                              new SqlParameter("@NguoiDungCapTrenID", SqlDbType.Int)
										 };
            // set the values
            parameters[0].Value = NguoiDungID;
            parameters[1].Value = QuyenID;
            parameters[2].Value = NguoiDungCapTrenID;

            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_insertQuyenNguoiDungbyQuyen", parameters, out numAffected, connec);

            return numAffected;
        }
        public int updateQuyenNguoiDungByQuyen(string NguoiDungIDs, int QuyenID, int NguoiDungCapTrenID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	 new SqlParameter("@NguoiDungIDs", SqlDbType.VarChar,2000),
											 new SqlParameter("@QuyenID", SqlDbType.Int),
                                              new SqlParameter("@NguoiDungCapTrenID", SqlDbType.Int)
										 };
            // set the values
            parameters[0].Value = NguoiDungIDs;
            parameters[1].Value = QuyenID;
            parameters[2].Value = NguoiDungCapTrenID;

            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_updateQuyenNguoiDungbyQuyen", parameters, out numAffected, connec);

            return numAffected;
        }
        //Xoa QuyenNguoiDung by QuyenID
        public int removeQuyenNguoiDung(int NguoiDungID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int)
                                        };
            parameters[0].Value = NguoiDungID;
            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_removeQuyenNguoiDung", parameters, out numAffected, connec);
            return numAffected;
        }
        /// <summary>
        /// Kiểm tra sự tồn tại của user name
        /// Created by  Linhdh 10/04/2012
        /// </summary>
        /// <param name="NguoiDungID"></param>
        /// <returns>true nếu không tồn tại; false nếu tồn tại</returns>
        public bool check_trung_username(string ip_str_user_name)
        {
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@UserName", SqlDbType.VarChar)
                                        };
            parameters[0].Value = ip_str_user_name;
            // run the procedure
            DataSet v_ds_nguoi_dung = RunProcedure("sp_Nguoidung_loadNguoidungByUserName", parameters, "TB_Nguoidung", connec);
            // Nếu đã tồn tại user trùng với user đang xét
            if (v_ds_nguoi_dung.Tables[0].Rows.Count > 0) return false;
            return true;
        }
        //Xoa QuyenNguoiDung by QuyenID
        public int removeQuyenNguoiDungbyQuyen(int QuyenID, int NguoiDungID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@QuyenID", SqlDbType.Int), 
                                        new SqlParameter("@NguoiDungID", SqlDbType.Int) 
                                        
                                        };
            parameters[0].Value = QuyenID;
            parameters[1].Value = NguoiDungID;
            // run the procedure
            RunProcedure("sp_QuyenNguoiDung_removeQuyenNguoiDungbyQuyen", parameters, out numAffected, connec);
            return numAffected;
        }
        public DataSet loadQuyenNguoidungbyNguoiDung(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoiDungID;

            DataSet dsQuyenNguoiDung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungByNguoiDung", parameters, "TB_QuyenNguoiDung", connec);
            return dsQuyenNguoiDung;
        }
        public DataSet loadQuyenByNguoiDung(int NguoiDungID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@NguoiDungID", SqlDbType.Int, 4) };
            parameters[0].Value = NguoiDungID;

            DataSet dsQuyenNguoiDung = RunProcedure("sp_QuyenNguoiDung_loadQuyenByNguoiDung", parameters, "TB_QuyenNguoiDung", connec);
            return dsQuyenNguoiDung;
        }
        public DataSet loadQuyenNguoiDungByQuyen(int QuyenID,int intUserID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@QuyenID", SqlDbType.Int, 4),new SqlParameter("@intUserID",SqlDbType.Int,4) };
            parameters[0].Value = QuyenID;
            parameters[1].Value = intUserID;
           
            DataSet dsNguoidung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungByQuyen", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
        public DataSet loadQuyenNguoiDungByNguoidungcaptren(int QuyenID, int NguoiDungCapTrenID)
        {
            // create the parameter
            SqlParameter[] parameters = { new SqlParameter("@QuyenID", SqlDbType.Int, 4), 
                                         new SqlParameter("@NguoiDungCapTrenID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = QuyenID;
            parameters[1].Value = NguoiDungCapTrenID;
            DataSet dsNguoidung = RunProcedure("sp_QuyenNguoiDung_loadQuyenNguoiDungBynguoidungcaptren", parameters, "TB_QuyenNguoiDung", connec);
            return dsNguoidung;
        }
        //public DataSet loadTimKiemNguoiDung(string i_str_ho_va_ten , string i_str_ten_dang_nhap)
        //{
        //    // create the parameter
        //    SqlParameter[] parameters = { new SqlParameter("@ho_va_ten", SqlDbType.NVarChar, 100), 
        //                                 new SqlParameter("@ten_dang_nhap", SqlDbType.NVarChar, 100) 
        //                                };
        //    parameters[0].Value = i_str_ho_va_ten;
        //    parameters[1].Value = i_str_ten_dang_nhap;
        //    DataSet dsNguoidung = RunProcedure("sp_dm_user", parameters, "TB_NguoiDung", connec);
        //    return dsNguoidung;
        //}
	}
}